clear all

*******************************
******************************
*global variables
global data "C:\Users\f1sxa08\Dropbox (ATL FRB)\TrumTax\data\CEX and SCF\CEX\CEXData"
global final_data "C:\Users\f1sxa08\Dropbox (ATL FRB)\TrumTax\data\CEX and SCF\CEX\CEX_final data"
*Setting the working directory
cd "$data"
*****************************
*Uploading the stata data
use "$data\2015_2018 interview survey data.dta"
**************************
*replacing for missing divsions using information from U.S. census Bureau
gen div = division if !missing(division)
replace div="1" if missing(division) & (state=="09"| state=="23"|state=="25"|state=="33"|state=="44"|state=="50")
replace div="2" if missing(division) & (state=="34"| state=="36"|state=="42")
replace div="3" if missing(division) & (state=="18"| state=="17"|state=="26"|state=="39"|state=="55")
replace div="4" if missing(division) & (state=="19"| state=="20"|state=="27"|state=="29"|state=="31"|state=="38"|state=="46")
replace div="5" if missing(division) & (state=="10"| state=="11"|state=="12"|state=="13"|state=="24"|state=="37"| state=="45"|state=="51"|state=="54")
replace div="6" if missing(division) & (state=="01"| state=="21"|state=="28"|state=="47")
replace div="7" if missing(division) & (state=="05"| state=="22"|state=="40"|state=="48")
replace div="8" if missing(division) & (state=="04"| state=="08"|state=="16"|state=="35"|state=="30"|state=="49"|state=="32"|state=="56")
replace div="9" if missing(division) & (state=="02"| state=="06"|state=="15"|state=="41"|state=="53")
drop division
gen division=real(div)
drop div
************************************
*destring quarter
gen quart=real(quarter)
drop quarter
rename quart quarter

*********************************************************
*Generating dummy for marital status 
gen married=0 if !missing(marital1)
replace married=1 if marital1=="1"
*****************************************
*generating a new rent paid variable
gen rentpaid=.
replace rentpaid=rntpaid if (rntpaid>0 &rntpaid<.)
*****************************************
*Generating a new variable from the retirements variables.
gen adjustments=.
replace adjustments=(retpencq+retpenpq)*4 if (retpencq >0 & retpencq<.| retpenpq>0 & retpenpq<.)
*******************************************
*Generating a new property tax variable
gen proptax=.
replace proptax=proptxpd if (proptxpd>0 & proptxpd<.)
***************************************

* Generating other items
gen otheritem=.
replace otheritem=amtdedct if (amtdedct>0 & amtdedct<.)
****************************************
*Generating a variable from childcare variables
gen childcare=.
replace childcare= chldcare if chldcare>0
**************************************
*Deleting observations that donot have values for the variable fincbtxm
drop if fincbtxm==.
**************************************
*Dropping observations with division missing
drop if division==.
***************************************
*Creating a quintile for each family based on fincbtxm
xtile quintile = fincbtxm, nq(5)
*label variable quintile
label var quintile "Total family income before taxes quintile"
******************************************
*Getting the weighted mean and median of the new variables created.

gen weights=int(round(finlwt21,0))

collapse (median) median_rentpaid=rentpaid median_adjustments=adjustments median_proptax=proptax median_otheritem=otheritem median_childcare=childcare ////
median_fincbtxm=fincbtxm ////
 (mean) mean_rentpaid=rentpaid mean_adjustments=adjustments mean_proptax=proptax mean_otheritem=otheritem mean_childcare=childcare ////
 mean_fincbtxm=fincbtxm [fw=weights], by (quintile quarter year division married)
 
 *************************
 ***Kepping observations only for the fourth quarter and dropping the quarter variable.
 keep if quarter==4
 drop quarter
 
 save "$final_data\data_TaxSim_CEX_Division_09_22_20", replace













